Client Report - Late Flights & Missing Data (JSON)

On this page

• Elevator pitch
• QUESTION|TASK 1
• QUESTION|TASK 2
• QUESTION|TASK 3
• QUESTION|TASK 4
• QUESTION|TASK 5

Elevator pitch

Using the data collected from the Bureau of Transportation Statistics I have determined what airports are the best to fly out of and when to fly in general to have the best chance of avoiding delays for several reasons altogether. Chicago O’Hare International Airport (ORD) was determined to be the worst airport based on the average length of delay (1.13 Hours). ORD also had the second highest proportion of delays due to weather out of all the airports I looked at. The highest proportion of delays due to weather was San Francisco International Airport (SFO). I also determined that September has the lowest proportion of total flights delayed. The holiday months (January, July, November, and December) have the highest likelihood of experiencing delays

# Learn morea about Code Cells: https://quarto.org/docs/reference/cells/cells-jupyter.html

# Include and execute your code here
url = "https://raw.githubusercontent.com/byuidatascience/data4missing/master/data-raw/flights_missing/flights_missing.json"
df = pd.read_json(url)

Airport_nan = df['airport_name'].isna().sum()
year_nan = df['year'].isna().sum()
min_delay_nan = df['minutes_delayed_carrier'].isna().sum()
min_delay_nas_nan = df['minutes_delayed_nas'].isna().sum()

year_nan

np.int64(23)

Highlight the Questions and Tasks

QUESTION|TASK 1

Fix all of the varied missing data types in the data to be consistent (all missing values should be displayed as “NaN”).

I went through and made a new data frame for me to preform some cleaning. I then went through and searched for all the blanks, 1500+ minutes, and values that had -999 in them. then used the “.replace” to get them out and replace them with “NaN”.

# Include and execute your code here

import pandas as pd
import numpy as np
from lets_plot import *

LetsPlot.setup_html(isolated_frame=True)

::: {#cell-Q1-Clean Data .cell execution_count=4}

# Include and execute your code here
df_clean = df

df_clean.replace(["", "1500+", -999], np.nan, inplace=True)

example = df_clean.iloc[2].to_json()
example

'{"airport_code":"IAD","airport_name":null,"month":"January","year":2005.0,"num_of_flights_total":12381,"num_of_delays_carrier":"414","num_of_delays_late_aircraft":1058.0,"num_of_delays_nas":895,"num_of_delays_security":4,"num_of_delays_weather":61,"num_of_delays_total":2430,"minutes_delayed_carrier":null,"minutes_delayed_late_aircraft":70919,"minutes_delayed_nas":35660.0,"minutes_delayed_security":208,"minutes_delayed_weather":4497,"minutes_delayed_total":134881}'

Cleaned Data

:::

QUESTION|TASK 2

Which airport has the worst delays?

I chose to look at the average time each flight is delayed determining the worst airport delays. In the table you will find the total delayed flights and total delayed minutes for each airport. The portion of delayed flights measure tells us what percentage of flights out of that airport are delayed. The last column displays the average hours each flight is delayed. This helped me determine that Chicago O’Hare International Airport (ORD) is the worst airport to fly out of.

# Include and execute your code here
import pandas as pd
import numpy as np
from lets_plot import *

LetsPlot.setup_html(isolated_frame=True)

# Include and execute your code here

df_clean_group = df_clean

grouped_data = df_clean_group.groupby('airport_code').agg(
  total_flights=('num_of_flights_total', 'sum'),
  total_delays=('num_of_delays_total', 'sum'),
  total_delay_minutes=('minutes_delayed_total', 'sum')
).reset_index()

grouped_data['proportion_delayed_flights'] = (grouped_data['total_delays'] / grouped_data['total_flights']) * 100

grouped_data['average_delay_hours'] = (grouped_data['total_delay_minutes'] / grouped_data['total_delays']) / 60

worst_airport = grouped_data.loc[grouped_data['average_delay_hours'].idxmax()]


worst_airport

airport_code                        ORD
total_flights                   3597588
total_delays                     830825
total_delay_minutes            56356129
proportion_delayed_flights    23.093945
average_delay_hours            1.130525
Name: 3, dtype: object

Worst Airport

::: {#cell-Q2-Airport Tables .cell .tbl-cap-location-top tbl-cap=‘Airport Delays’ execution_count=7}

# Include and execute your code here
grouped_data

	airport_code	total_flights	total_delays	total_delay_minutes	proportion_delayed_flights	average_delay_hours
0	ATL	4430047	902443	53983926	20.370958	0.996996
1	DEN	2513974	468519	25173381	18.636589	0.895495
2	IAD	851571	168467	10283478	19.783083	1.017358
3	ORD	3597588	830825	56356129	23.093945	1.130525
4	SAN	917862	175132	8276248	19.080428	0.787620
5	SFO	1630945	425604	26550493	26.095546	1.039718
6	SLC	1403384	205160	10123371	14.618950	0.822396

:::

QUESTION|TASK 3

What is the best month to fly if you want to avoid delays of any length?

I chose to look at the proportion of delayed flights to determine what month is the best month to fly. I chose this as my metric because I feel this paints an accurate picture about what the chances are of your flight being delayed dependent on month. From what I found, the best month to fly is September. 16.5% of all the flights that occur in September end up being delayed.

# Include and execute your code here
import pandas as pd
import numpy as np
from lets_plot import *

LetsPlot.setup_html(isolated_frame=True)

# Include and execute your code here
df_mo = df_clean

df_mo['month'] = df_mo['month'].replace('Febuary', 'February')

df_clean_month = df_mo[df['month'].notna()]

month_order = ['January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December']

df_clean_month['month'] = pd.Categorical(df_clean_month['month'], categories=month_order, ordered=True)

monthly_delays = df_clean_month.groupby('month').agg(
  total_flights=('num_of_flights_total', 'sum'),
  total_delays=('num_of_delays_total', 'sum'),
  total_delay_minutes=('minutes_delayed_total', 'sum')
).reset_index()

monthly_delays['proportion_delayed_flights'] = (monthly_delays['total_delays'] / monthly_delays['total_flights']) * 100

(
  ggplot(monthly_delays, aes(x='month', y='proportion_delayed_flights')) +
  geom_bar(stat='identity') +
  ggtitle('Proportion of Delayed Flights by Month') +
  xlab('Month') +
  ylab('Proportion of Delayed Flights (%)') +
  theme(axis_text_x=element_text(angle=45, hjust=1))
)

Proportion of Delayed Flights by Month

QUESTION|TASK 4

Your job is to create a new column that calculates the total number of flights delayed by weather (both severe and mild).

I was able to create the new row and add it to the table. The column “weather_related_delays” will be used for the next question.

# Include and execute your code here

import pandas as pd
import numpy as np
from lets_plot import *

LetsPlot.setup_html(isolated_frame=True)

# Include and execute your code here

df_clean_two = df_clean

df_clean_two['num_of_delays_late_aircraft'] = df_clean_two['num_of_delays_late_aircraft'].replace(-999, np.nan)

df_clean_two['num_of_delays_late_aircraft'].fillna(df['num_of_delays_late_aircraft'].mean(), inplace=True)

def weather_nas_delays(row):
  if row['month'] in ['April', 'May', 'June', 'July', 'August']:
    return 0.4 * round(row['num_of_delays_nas'], 2)
  else:
    return 0.65 * round(row['num_of_delays_nas'], 2)

df_clean_two['weather_related_delays'] = (
  df_clean_two['num_of_delays_weather'] +
  round(0.3 * df_clean['num_of_delays_late_aircraft'], 2) +
  df_clean_two.apply(weather_nas_delays, axis=1)
)

df_clean_two.head(5)

	airport_code	airport_name	month	year	num_of_flights_total	num_of_delays_carrier	num_of_delays_late_aircraft	num_of_delays_nas	num_of_delays_security	num_of_delays_weather	num_of_delays_total	minutes_delayed_carrier	minutes_delayed_late_aircraft	minutes_delayed_nas	minutes_delayed_security	minutes_delayed_weather	minutes_delayed_total	weather_related_delays
0	ATL	Atlanta, GA: Hartsfield-Jackson Atlanta Intern...	January	2005.0	35048	NaN	1109.104072	4598	10	448	8355	116423.0	104415	207467.0	297	36931	465533	3769.43
1	DEN	Denver, CO: Denver International	January	2005.0	12687	1041	928.000000	935	11	233	3153	53537.0	70301	36817.0	363	21779	182797	1119.15
2	IAD	NaN	January	2005.0	12381	414	1058.000000	895	4	61	2430	NaN	70919	35660.0	208	4497	134881	960.15
3	ORD	Chicago, IL: Chicago O'Hare International	January	2005.0	28194	1197	2255.000000	5415	5	306	9178	88691.0	160811	364382.0	151	24859	638894	4502.25
4	SAN	San Diego, CA: San Diego International	January	2005.0	7283	572	680.000000	638	7	56	1952	27436.0	38445	21127.0	218	4326	91552	674.70

Weather Delays

QUESTION|TASK 5

Using the new weather variable calculated above, create a barplot showing the proportion of all flights that are delayed by weather at each airport. Describe what you learn from this graph.

According the the data San Francisco International Airport (SFO) experiences the highest proportion of delays due to bad weather days. Approximately 9.81% of the flights from SFO are delayed due to weather. Upon further research I found that San Francisco experiences frequent oceanic fog that causes delays and groundings for outbound flights. The next airport to avoid flying out of is Chicago O’Hare International Airport (ORD) with 8.5% of their flights become delayed.

# Include and execute your code here

import pandas as pd
import numpy as np
from lets_plot import *

LetsPlot.setup_html(isolated_frame=True)

# Include and execute your code here
df_clean_three = df_clean_two

df_clean_three['proportion_weather_delays'] = (df_clean_three['weather_related_delays'] / df_clean_three['num_of_flights_total']) * 100

airport_weather_delays = df_clean_three.groupby('airport_code').agg(proportion_weather_delays=('proportion_weather_delays', 'mean')
).reset_index()

(
  ggplot(airport_weather_delays, aes(x='airport_code', y='proportion_weather_delays')) +
  geom_bar(stat='identity') +
  ggtitle('Proportion of Weather Delays by Airport') +
  xlab('Airport Code') +
  ylab('Proportion of Weather Delays (%)') +
  theme(axis_text_x=element_text(angle=45, hjust=1))
)

Proportion of Weather Delays by Airport